# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 
# Message:  清洗好sa_desc 表之后, 清洗新的part_num表

from MysqlTools import MysqlTools
import json
from try_ import format_ex


class NewPartNum:

    def __init__(self, **kwargs):
        self.db = MysqlTools(db_config=kwargs.get("db_config"), type='dict', auto_commit=10000)
        self.old_part_table = kwargs.get("old_part_table")
        self.struc_table = kwargs.get("struc_table")
        self.sa_desc_table = kwargs.get("sa_desc_table")

    def construct_sa_desc_dict(self, table):
        sql = '''select * from `{}`'''.format(table)
        data = self.db.select_all(sql)
        _dict = {}
        for info in data:
            sa = info.get("sa")
            desc = info.get("desc").upper().replace(" ", "")
            _dict[desc] = sa
        return _dict

    def get_sa(self, list):
        _l = []
        for i in list:
            sa = self.sa_desc_dict.get(i, "")
            if sa == '':
                continue
            _l.append(sa)
        return _l

    def run(self):
        self.sa_desc_dict = self.construct_sa_desc_dict(self.sa_desc_table)
        sql = '''select * from `{}`;'''.format(self.old_part_table)
        data = self.db.select_all(sql)
        for info in data:
            id = info.get("id")
            extra_info = json.loads(info.get("extra_info"))
            microappl = extra_info.get("microappl")
            _l = format_ex(microappl)
            for i in _l:
                exc = i.get("exc_sa")
                exc_sa = self.get_sa(exc)
                app = i.get("app_sa")
                app_sa = self.get_sa(app)
                i['exc_sa'] = exc_sa
                i['app_sa'] = app_sa
            extra_info['filter_rules'] = _l
            new_ex_info = json.dumps(extra_info, ensure_ascii=False)
            up_sql = '''update `{}` set `extra_info`='{}' where `id`="{}";'''.format(self.old_part_table, new_ex_info, id)
            self.db.execute_sql(up_sql)
        self.db.commit_sql()

    def run_struc(self):
        self.sa_desc_dict = self.construct_sa_desc_dict(self.sa_desc_table)
        sql = '''select * from `{}`'''.format(self.struc_table)
        data = self.db.select_all(sql)
        for info in data:
            id = info.get("id")
            description = info.get("description")
            if description.strip('- ') == '':
                continue
            _dict = {}
            filter_rules = format_ex(description)
            for i in filter_rules:
                exc = i.get("exc_sa")
                app = i.get("app_sa")
                exc_sa = self.get_sa(exc)
                app_sa = self.get_sa(app)
                i['exc_sa'] = exc_sa
                i['app_sa'] = app_sa
            _dict['filter_rules'] = filter_rules
            up_sql = '''update `{}` set `extra_info`='{}' where `id`="{}"'''.format(self.struc_table, json.dumps(_dict, ensure_ascii=False), id)
            self.db.execute_sql(up_sql)
        self.db.commit_sql()

    def get_part_num_rules(self, table):
        sql = '''select `extra_info` from `{}`;'''.format(table)
        data = self.db.select_all(sql)
        _l= []
        for info in data:
            extra_info = json.loads(info.get("extra_info"))
            microappl = extra_info.get("microappl")
            rules = format_ex(microappl, return_rules=True)
            for r in rules:
                _l.append(r)
        return set(_l)

    def get_struc_rules(self, table):
        sql = '''select `description` from `{}`;'''.format(table)
        data = self.db.select_all(sql)
        _l = []
        for info in data:
            description = info.get("description")
            rules = format_ex(description, return_rules=True)
            for r in rules:
                _l.append(r)
        return set(_l)

    def get_vin_rules(self, table):
        sql = '''select `extra_info` from `{}`'''.format(table)
        data = self.db.select_all(sql)
        _l = []
        for info in data:
            extra_info = json.loads(info.get("extra_info"))
            app_info = extra_info.get("app_info")
            for a in app_info:
                _l.append(a)
        return set(_l)

    def construct_sa_desc(self):
        # 从不同的表中提取条件,转化成desc
        part_num_rules = self.get_part_num_rules('cars_part_num')
        print('1')
        struc_num_rules = self.get_struc_rules('cars_structure_collection')
        print('2')
        vin_rules = self.get_vin_rules('cars_vin_info_new')
        print('3')
        _all = part_num_rules.union(struc_num_rules).union(vin_rules)
        num = 666
        for desc in _all:
            sa = 'CP%06d' % num
            num += 1
            _dict = dict(
                sa=sa,
                desc=desc
            )
            insql = self.db.get_insert_sql('sa_desc', _dict)
            self.db.execute_sql(insql)
        self.db.commit_sql()

    def merge_desc(self):
        _all = set()
        for tb in ['sa_desc_c', 'sa_desc_p']:
            sql = 'select * from `{}`'.format(tb)
            data = self.db.select_all(sql)
            for info in data:
                desc = info.get("desc")
                _all.add(desc)
        num = 666
        for desc in _all:
            sa = 'CP%06d' % num
            num += 1
            _dict = dict(
                sa=sa,
                desc=desc
            )
            insql = self.db.get_insert_sql('sa_desc_merge', _dict)
            self.db.execute_sql(insql)
        self.db.commit_sql()

if __name__ == '__main__':
    db_config = dict(
        host="192.168.10.166",
        port=3306,
        user="a",
        passwd="110",
        db="citroen",
        charset="utf8",
    )
    sa_desc_table = 'sa_desc_merge'
    old_part_table = 'cars_part_num_new'
    struc_table = 'cars_structure_collection_new'
    tools = NewPartNum(db_config=db_config, old_part_table=old_part_table, struc_table=struc_table, sa_desc_table=sa_desc_table)
    # tools.run()
    # tools.run_struc()
    _dict = tools.construct_sa_desc_dict(sa_desc_table)
    with open('./sa_desc.py', 'w') as s:
        s.write(json.dumps(_dict, ensure_ascii=False, indent=4))
    # tools.construct_sa_desc()
    # tools.merge_desc()

